﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;
using Portal.Database.Service;
using Portal.Systems.Model;
using Portal.Database.Utility;
using Portal.Utility;
using Portal.Web.Utility;
using Portal.Core;
using Portal.Systems.Bussiness.Template;

namespace Portal.Systems.Bussiness
{
    public class UserProcessBll
    {
        public static UserCls[] ReadingUsers(string SiteId, string RoleId, string Keyword)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            string DataLanguage = WebConfig.GetDataLanguage(OSiteParam);
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query =
                    " select TableUser.* from TableUser " +
                    "     where AssetCode=@AssetCode ";
            if (!string.IsNullOrEmpty(RoleId))
            {
                Query += " and UserId in (select frkUserId from TableUserRole where frkRoleId='" + RoleId + "')";
            }
            if (!string.IsNullOrEmpty(Keyword))
            {
                Query += " and (FullName like N'%" + Keyword + "%' or LoginName like N'%" + Keyword + "%')";
            }
            Query+=
                    " order by LoginName";
            DataSet dsResult =
                    DBService.GetDataSet(null, Query,
                    new DbParam[] 
                    { 
                        new DbParam("AssetCode", AssetCode),
                    });
            if (dsResult.Tables[0].Rows.Count == 0)
            {
                return new UserCls[0];
            }
            UserCls[] OUser = UserParser.ParseFromDataTable(dsResult.Tables[0]);
            dsResult.Clear();
            dsResult.Dispose();
            return OUser;
        }

        public static UserCls[] ReadingUserForDatabaseFolderPermission(string SiteId, string DatabaseFolderId, string Keyword)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            string DataLanguage = WebConfig.GetDataLanguage(OSiteParam);
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query =
                    " select TableUser.* from TableUser " +
                    "     where AssetCode=@AssetCode and UserId not in (select frkUserId from TableDatabaseFolderPermission where not frkUserId is null and frkDatabaseFolderId=@DatabaseFolderId)";
            if (!string.IsNullOrEmpty(Keyword))
            {
                Query += " and (FullName like N'%" + Keyword + "%' or LoginName like N'%" + Keyword + "%')";
            }
            Query+=
                    " order by LoginName";
            DataSet dsResult =
                    DBService.GetDataSet(null, Query,
                    new DbParam[] 
                    { 
                        new DbParam("AssetCode", AssetCode),
                        new DbParam("DatabaseFolderId", DatabaseFolderId),
                    });
            if (dsResult.Tables[0].Rows.Count == 0)
            {
                return new UserCls[0];
            }
            UserCls[] OUser = UserParser.ParseFromDataTable(dsResult.Tables[0]);
            dsResult.Clear();
            dsResult.Dispose();
            return OUser;
        }



        public static UserCls[] ReadingUserForDatabaseFieldPermission(
            string SiteId, 
            string DatabaseFieldId, 
            string Keyword)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            string DataLanguage = WebConfig.GetDataLanguage(OSiteParam);
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query =
                    " select TableUser.* from TableUser " +
                    "     where AssetCode=@AssetCode and UserId not in (select frkUserId from TableDatabaseFieldPermission where not frkUserId is null and frkDatabaseFieldId=@DatabaseFieldId)";
            if (!string.IsNullOrEmpty(Keyword))
            {
                Query += " and (FullName like N'%" + Keyword + "%' or LoginName like N'%" + Keyword + "%')";
            }
            Query +=
                    " order by LoginName";
            DataSet dsResult =
                    DBService.GetDataSet(null, Query,
                    new DbParam[] 
                    { 
                        new DbParam("AssetCode", AssetCode),
                        new DbParam("DatabaseFieldId", DatabaseFieldId),
                    });
            if (dsResult.Tables[0].Rows.Count == 0)
            {
                return new UserCls[0];
            }
            UserCls[] OUser = UserParser.ParseFromDataTable(dsResult.Tables[0]);
            dsResult.Clear();
            dsResult.Dispose();
            return OUser;
        }



        public static UserCls[] ReadingAvailableUser(string SiteId, string RoleId, string Keyword)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            string DataLanguage = WebConfig.GetDataLanguage(OSiteParam);
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query =
                    " select TableUser.*,AssetName from TableUser left outer join TableAsset on TableAsset.AssetCode=TableUser.AssetCode " +
                    " where TableUser.AssetCode=@AssetCode " +
                    "       and UserId not in (select frkUserId from TableUserRole where frkRoleId='"+RoleId+"') ";
            
            if (!string.IsNullOrEmpty(Keyword))
            {
                Query += " and (FullName like N'%" + Keyword + "%' or LoginName like N'%" + Keyword + "%')";
            }
            Query +=
                    " order by LoginName";
            DataSet dsResult =
                    DBService.GetDataSet(null, Query,
                    new DbParam[] 
                    { 
                        new DbParam("AssetCode", AssetCode),
                    });
            if (dsResult.Tables[0].Rows.Count == 0)
            {
                return new UserCls[0];
            }
            UserCls[] OUser = UserParser.ParseFromDataTable(dsResult.Tables[0]);
            dsResult.Clear();
            dsResult.Dispose();
            return OUser;
        }

        public static UserCls[] ReadingUserBelongRole(string SiteId, string RoleId, string Keyword)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            string DataLanguage = WebConfig.GetDataLanguage(OSiteParam);
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query =
                    " select TableUser.* from TableUser " +
                    "  where AssetCode=@AssetCode and " +
                    "    UserId in (select frkUserId from TableUserRole where frkRoleId='"+RoleId+"') ";

            if (!string.IsNullOrEmpty(Keyword))
            {
                Query += " and (FullName like N'%" + Keyword + "%' or LoginName like N'%" + Keyword + "%')";
            }
            Query +=
                    " order by LoginName";
            DataSet dsResult =
                    DBService.GetDataSet(null, Query,
                    new DbParam[] 
                    { 
                        new DbParam("AssetCode", AssetCode),
                    });
            if (dsResult.Tables[0].Rows.Count == 0)
            {
                return new UserCls[0];
            }
            UserCls[] OUser = UserParser.ParseFromDataTable(dsResult.Tables[0]);
            dsResult.Clear();
            dsResult.Dispose();
            return OUser;
        }

        public static UserCls CreateUserModel(string SiteId, string UserId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            DataSet dsResult =
                    DBService.GetDataSet(null, "select TableUser.*,AssetName from TableUser " +
                                               "        join TableAsset on TableAsset.AssetCode=TableUser.AssetCode" +
                                               "     where (UserId=@UserId or LoginName=@UserId)",
                    new DbParam[] 
                    { 
                        new DbParam("UserId", UserId),
                    });
            if (dsResult.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            UserCls OUser = UserParser.ParseFromDataRow(dsResult.Tables[0].Rows[0]);
            dsResult.Clear();
            dsResult.Dispose();
            return OUser;
        }


        public static UserCls CheckHasUser(string SiteId, string AssetCode, string LoginName)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            DataSet dsResult =
                    DBService.GetDataSet(null, "select TableUser.*,AssetName from TableUser " +
                                               "        join TableAsset on TableAsset.AssetCode=TableUser.AssetCode" +
                                               "     where (TableAsset.AssetCode=@AssetCode and LoginName=@LoginName)",
                    new DbParam[] 
                    { 
                        new DbParam("AssetCode", AssetCode),
                        new DbParam("LoginName", LoginName),
                    });
            if (dsResult.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            UserCls OUser = UserParser.ParseFromDataRow(dsResult.Tables[0].Rows[0]);
            dsResult.Clear();
            dsResult.Dispose();
            return OUser;
        }

        public static UserCls CheckForLogin(string SiteId, string AssetCode, string LoginName, string Password, ref int LoginAttempCount, ref string gLoginName)
        {            
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);

            DataSet dsResult = new DataSet();
            dsResult =
               DBService.GetDataSet(null, " select * from TableUser " +
                                          " where LoginName=@LoginName ",
               new DbParam[] 
                    { 
                        new DbParam("LoginName", LoginName),                        
                    });

            UserCls OUser = new UserCls();

            if (dsResult.Tables[0].Rows.Count > 0)
            {
                OUser = UserParser.ParseFromDataRow(dsResult.Tables[0].Rows[0]);
                TimeSpan diff1 = DateTime.Now.Subtract(OUser.FailLoginTime);

                if (OUser.Locked == 1)
                {
                    SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
                    string BlockTime = WebConfig.GetConfigFromXml(OSiteParam, "BlockTime");
                    if (diff1.TotalMinutes > int.Parse(BlockTime))
                    {
                        BlockUser(SiteId, OUser.UserId, 0);
                        UpdateLoginUser(SiteId, OUser.UserId, OUser.LoginName, 0);
                    }
                    else
                    {
                        throw new Exception("Tài khoản của bạn đã bị khóa, vui lòng liên hệ với quản trị hệ thống!");
                    }
                }
            }


            dsResult = new DataSet();
            dsResult = DBService.GetDataSet(null, "select TableUser.*,AssetName, TableUserRole.frkRoleId as 'RoleId' from TableUser" +
                                               "        join TableAsset on TableAsset.AssetCode=TableUser.AssetCode"+
                                               "        join [TableUserRole] on TableUserRole.frkUserId = TableUser.UserId" +
                                               "     where LoginName=@LoginName and TableUser.AssetCode=@AssetCode and Password=@Password",
                    new DbParam[] 
                    { 
                        new DbParam("LoginName", LoginName),
                        new DbParam("AssetCode", AssetCode),
                        new DbParam("Password", Password),
                    });

            
            if (dsResult.Tables[0].Rows.Count == 0)
            {
                dsResult = new DataSet();
                dsResult =
                   DBService.GetDataSet(null, " select * from TableUser " +
                                              " where LoginName=@LoginName ",
                   new DbParam[] 
                    { 
                        new DbParam("LoginName", LoginName),                        
                    });

                if (dsResult.Tables[0].Rows.Count > 0)
                {
                    try
                    {
                        LoginAttempCount = int.Parse(WebSessionUtility.GetSession(SiteId, OUser.UserId).ToString());
                    }
                    catch
                    {
                        LoginAttempCount = 0;
                    }
                    LoginAttempCount += 1;
                    gLoginName = LoginName;
                    WebSessionUtility.SetSession(SiteId, OUser.UserId, LoginAttempCount);
                    OUser = UserParser.ParseFromDataRow(dsResult.Tables[0].Rows[0]);
                    UpdateLoginUser(SiteId, OUser.UserId, OUser.LoginName, LoginAttempCount);

                    if (LoginAttempCount > 3)
                    {
                        BlockUser(SiteId, OUser.UserId, 1);
                        throw new Exception("Bạn đã đăng nhập không thành công quá 3 lần, tài khoản sẽ bị khóa trong vòng 100 phút!");
                    }
                }
                return null;
            }

            OUser = UserParser.ParseFromDataRow(dsResult.Tables[0].Rows[0]);
            
            if (OUser.Locked == 1)
            {
                throw new Exception("Tài khoản của bạn đã bị khóa, vui lòng liên hệ với quản trị hệ thống!");             
            }

            if (!OUser.Password.Equals(Password))
            {
                throw new Exception("Mật khẩu nhập không đúng!");
            }
           
            dsResult.Clear();
            dsResult.Dispose();
            return OUser;
        }


        public static void AddUser(
            string SiteId, 
            string AssetCode, 
            string UserId,
            string LoginName, 
            string FullName, 
            string Email,
            string Password,
            string ReTypePassword,
            string[] RoleIds,
            bool   CheckValid)
        {
            if (!Password.Equals(ReTypePassword)) throw new Exception("Mật khẩu nhập lại không chính xác!");
            if (!SecurityUtility.IsPasswordStrong(Password)) throw new Exception("Mật khẩu quá đơn giản xin vui lòng chọn mật khẩu dài tối thiểu 6 kí tự và bao gồm cả chữ lẫn số!");

            //kiem tra xem co chua
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            if (CheckValid)
            {
                string CheckQuery = "select UserId from TableUser where LoginName=@LoginName";
                DataTable dtCheck = DBService.GetDataTable(null, CheckQuery, new DbParam[]{
                    new DbParam("LoginName",LoginName),
                });
                if (dtCheck.Rows.Count > 0)
                {
                    throw new Exception("Tài khoản đã tồn tại. Xin chọn tên đăng nhập khác!");
                }

                CheckQuery = "select UserId from TableUser where Email=@Email";
                dtCheck = DBService.GetDataTable(null, CheckQuery, new DbParam[]{
                    new DbParam("Email",Email),
                });
                if (dtCheck.Rows.Count > 0)
                {
                    throw new Exception("Email đã tồn tại. Xin chọn email khác!");
                }
            }
            if (string.IsNullOrEmpty(UserId))
            {
                UserId = System.Guid.NewGuid().ToString();
            }
            IDbTransaction Trans= DBService.BeginTransaction();
            try
            {
                DBService.Insert(Trans, "TableUser",
                        new DbParam[] 
                    { 
                        new DbParam("UserId", UserId),
                        new DbParam("LoginName", LoginName),
                        new DbParam("FullName", FullName),
                        new DbParam("Password", Password),
                        new DbParam("Email", Email),
                        new DbParam("AssetCode", AssetCode),
                        new DbParam("EntryDate", System.DateTime.Now),
                    });
                if (RoleIds != null)
                {
                    for (int iIndex = 0; iIndex < RoleIds.Length; iIndex++)
                    {
                        AddUserToRole(Trans, SiteId, RoleIds[iIndex], UserId);
                    }
                }
                Trans.Commit();
            }
            catch (Exception ex)
            {
                Trans.Rollback();
                throw (ex);
            }
        }


        public static void AddUserToRole(
            IDbTransaction Trans,
            string SiteId,
            string RoleId,
            string UserId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            string UserRoleId = System.Guid.NewGuid().ToString();
            DBService.Insert(Trans, "TableUserRole",
                    new DbParam[] 
                    { 
                        new DbParam("UserRoleId", UserRoleId),
                        new DbParam("frkRoleId", RoleId),
                        new DbParam("frkUserId", UserId),
                    });
        }



        public static void RemoveUserFromRole(
            string SiteId,
            string RoleId,
            string UserId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            string Query = "Delete from TableUserRole where frkRoleId=@RoleId and frkUserId=@UserId";
            DBService.ExecuteNonQuery(null, Query, new DbParam[]{
                new DbParam("RoleId",RoleId),
                new DbParam("UserId",UserId),
            });
        }

        public static void SaveUser(string SiteId, string UserId, string LoginName, string FullName,string RoleId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);

            
            DBService.Update(null, "TableUser", "UserId", UserId,
                    new DbParam[] 
                    { 
                        new DbParam("LoginName", LoginName),
                        new DbParam("FullName", FullName),
                    });

            

            if (!string.IsNullOrEmpty(RoleId))
            {
                AddUserToRole(null, SiteId, RoleId, UserId);
            }
        }


        public static void BlockUser(string SiteId, string UserId, int LockVal)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            DBService.Update(null, "TableUser", "UserId", UserId,
                    new DbParam[] 
                    { 
                        new DbParam("Locked", LockVal)                        
                    });
        }

        public static void DeleteUser(string SiteId, string UserId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            string Query =
                " Delete from TableUserType where frkUserId=@UserId; " +
                " Delete from TableUserRole where frkUserId=@UserId; " +
                " Delete from TableUser where UserId=@UserId; ";
            DBService.ExecuteNonQuery(null, Query,
                    new DbParam[] 
                    { 
                        new DbParam("UserId", UserId),
                    });
        }


        public static void ChangePassword(string SiteId, string AssetCode,string LoginName, string Password, string RePassword)
        {
            if (!Password.Equals(RePassword)) throw new Exception("Mật khẩu nhập lại không đúng!");
            bool IsPasswordOk = SecurityUtility.IsPasswordStrong(Password);
            if (!IsPasswordOk)
            {
                throw new Exception("Mật khẩu cần dài tối thiểu 6 kí tự và bao gồm cả chữ lẫn số!");
            }
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            string Query =
                " Update TableUser set Password=@Password where AssetCode=@AssetCode and LoginName=@LoginName ";
            DBService.ExecuteNonQuery(null, Query,
                    new DbParam[] 
                    { 
                        new DbParam("AssetCode", AssetCode),
                        new DbParam("LoginName", LoginName),
                        new DbParam("Password", Password),
                    });
        }

        //public static string GetSelectRole(string SiteId)
        //{
        //    try
        //    {
        //        RoleCls[] Roles = RoleProcessBll.ReadingRolesForUser(SiteId, WebSessionUtility.GetCurrentLoginUser().UserId);
        //        if (Roles.Length == 0)
        //        {
        //            WebSessionUtility.GetCurrentLoginUser().RoleId = "";
        //        }
        //        else
        //        {
        //            if (string.IsNullOrEmpty(WebSessionUtility.GetCurrentLoginUser().RoleId))
        //            {
        //                WebSessionUtility.GetCurrentLoginUser().RoleId = Roles[0].RoleId;
        //            }
        //        }
                
        //        string SelectRoleText = 
        //            "<script language=javascript>\r\n"+
        //            "   function ChangeRole()\r\n"+
        //            "   {\r\n"+
        //            "       SiteId='"+SiteId+"';\r\n"+
        //            "       RoleId=document.getElementById('drpSelectRoleForMenu').value;\r\n"+
        //            "       AjaxOut = Portal.Admin.Screen.Render.ProcessAjaxHandlerBll.ServerSideChangeRole(SiteId,RoleId).value;\r\n" +
        //            "       if(AjaxOut.Error)\r\n"+
        //            "       {\r\n"+
        //            "           alert(AjaxOut.InfoMessage);\r\n"+
        //            "           return;\r\n"+
        //            "       }\r\n"+
        //            "       window.open(AjaxOut.RetUrl);\r\n"+
        //            "   }\r\n"+
        //            "</script>\r\n"+
        //            "<select id=\"drpSelectRoleForMenu\" onchange=\"javascript:ChangeRole();\">\r\n";
        //        for (int iIndex = 0; iIndex < Roles.Length; iIndex++)
        //        {
        //            if (WebSessionUtility.GetCurrentLoginUser().RoleId.Equals(Roles[iIndex].RoleId))
        //            {
        //                SelectRoleText += "   <option selected value=\"" + Roles[iIndex].RoleId + "\">" + Roles[iIndex].RoleName + "</option>\r\n";
        //            }
        //            else
        //            {
        //                SelectRoleText += "   <option value=\"" + Roles[iIndex].RoleId + "\">" + Roles[iIndex].RoleName + "</option>\r\n";
        //            }
        //        }
        //        SelectRoleText += "</select>\r\n";
        //        return SelectRoleText;
        //    }
        //    catch (Exception ex)
        //    {
        //        return ex.Message.ToString();
        //    }
        //}

        public static void UpdateLoginUser(string SiteId, string UserId, string LoginName, int FailLoginNumber)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);


            DBService.Update(null, "TableUser", "UserId", UserId,
                    new DbParam[] 
                    { 
                        new DbParam("LoginName", LoginName),
                        new DbParam("FailLoginNumber", FailLoginNumber),
                        new DbParam("FailLoginTime", DateTime.Now,DbType.DateTime),
                    });
        }
    }
}

